package systempen.cmanager;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import android.R.integer;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.DialogInterface;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;
import android.widget.Toast;



public class util {
	public SQLiteDatabase db;

	public static void showDialog(String str, Context a){
		AlertDialog alertDialog = new AlertDialog.Builder(a).create();
		alertDialog.setTitle("Error...");
		alertDialog.setMessage(str.toString());
		alertDialog.setButton2("OK", new DialogInterface.OnClickListener() {
			public void onClick(DialogInterface dialog, int which) {} 
		}
				);

		alertDialog.show(); 
	}

	public static String getDefaultAccountName(Context c){
		String sql = "select ifnull(a.description,\'Default account\') a "+
				"from setings s join account a on s.v = a.a "+
				"where s.k = \'DEFAULTACCOUNT\' ";
		return util.getFirstDataString(sql, c);
	}
	public static String getDefaultAccountCode(Context c){
		String sql = "select ifnull(a.a,\'1\') "+
				"from setings s join account a on s.v = a.a "+
				"where s.k = \'DEFAULTACCOUNT\' ";
		return util.getFirstDataString(sql, c);
	}

	public static void execSQL(String sql, Context a){
		SQLiteDatabase db;
		String path = a.getApplicationContext().getDatabasePath("DBS").toString();
		db = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		try{
			db.execSQL(sql);
			db.close();
		}catch (Exception ex){
			util.showDialog(ex.getMessage().toString(), a);
		}
	}
	public void initializeTables(Context a){
		SQLiteDatabase db = a.openOrCreateDatabase(a.getApplicationContext().getDatabasePath("DBS").toString(), android.content.Context.MODE_PRIVATE , null);
		db.close();//zatvori i uzivaj
		execSQL("CREATE TABLE IF NOT EXISTs cost_type (code integer primary key, name text, parent integer)", a);
		String s;
		s = "CREATE TABLE IF NOT EXISTs log (" +
				" a integer primary KEY, " +
				" cost_code integer," +
				" value real," +
				" whe date," +
				" account integer," +
				" description varchar(200))"; 
		execSQL(s, a);
		s = "CREATE TABLE IF NOT EXISTS account ("+
				" a integer primary KEY, description varchar(20))";
		execSQL(s, a);
		s = "CREATE TABLE IF NOT EXISTS setings ("+
				" a integer primary key, "+
				" k varchar(50), "+ //key
				" v varchar(50) "+ //value
				")";
		execSQL(s, a);

		s = "CREATE TABLE IF NOT EXISTS password ( "+
		" pass varchar(100), "+ 
		" question varchar(100), " +
		" answer varchar(100))";
		execSQL(s, a);

		//s = util.getFirstDataString("select 1 from setings where k='DEFAULTPERIOD'", a);
		//if (!s.equals(""))
		//s = " delete from setings";
		//execSQL(s, a);


		s = "insert into setings (k,v) "+
				" select 'DEFAULTPERIOD','THISMONTH' "+
				" where not exists (select 1 from setings where k='DEFAULTPERIOD')";
		execSQL(s, a);

		s = "insert into setings (k,v) "+
				" select 'DEFAULTACCOUNT','1' "+
				" where not exists (select 1 from setings where k='DEFAULTACCOUNT')";
		execSQL(s, a);

		//old data, 2 ista sloga
		s = "select count(*) from account where description = \'Credit card\' and a in (1,2)";
		String koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("2".toString()))
		{
			s = "delete from account";
			util.execSQL(s, a);
		}


		s = "insert into Account (description) "+
				" select 'Default account' "+
				" where not exists (select 1 from account where a=1)";//Ako nema prvog sloga ti ga ubaci
		execSQL(s, a);

		s = "insert into Account (description) "+
				" select 'Credit card' "+
				" where not exists (select 1 from account where a=2)";//Ako nema Drugog sloga ti ga ubaci
		execSQL(s, a);

		//dodamo ako treba kolonu orde. Znam da fali r, ali to je valjda kljucna rec.
		s = "select count(*) from sqlite_master where tbl_name = 'cost_type' and sql like '%orde%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table cost_type add orde int";
			util.execSQL(s, a);
		}

		//dodamo ako treba kolonu path. Sluzi za cuvanje path-a, da ne mora svaki put da se racuna, traje uzas sporo
		s = "select count(*) from sqlite_master where tbl_name = 'cost_type' and sql like '%path%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table cost_type add path text";
			util.execSQL(s, a);
		}
		
		s = "select count(*) from sqlite_master where tbl_name = 'log' and sql like '%picture%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table log add picture varchar(100)";
			util.execSQL(s, a);
		}
		

		AzurirajPraznePathove(a);

		s = "update cost_type set orde = code where orde is null"; //inicijalizacija
		util.execSQL(s, a);


		//trigger updateuje kolonu za order by [orde] tako da nije prazna.
		s = "CREATE trigger if not exists trg_cost_type after insert on cost_type "+ 
				"	begin "+
				"     update cost_type set orde = code where code = new.code or orde is null; "+
				"   end";
		util.execSQL(s, a);

		if (!typesInserted(a)){
			insertDefaultCostTypes(a);
		}

		s = "CREATE TABLE IF NOT EXISTS budget ( "+
				" a integer primery KEY, "+
				" cost_type integer,"+
				" acocunt integer"+
				" amount real, "+
				" date_from date, "+
				" date_to date "+
				" ) ";
		util.execSQL(s, a);

		s = "select count(*) from sqlite_master where tbl_name = 'budget' and sql like '%account%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table budget add account int";
			util.execSQL(s, a);
		}

		s = "select count(*) from sqlite_master where tbl_name = 'budget' and sql like '%description%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table budget add description varchar(200)";
			util.execSQL(s, a);
		}
		s = "select count(*) from sqlite_master where tbl_name = 'budget' and sql like '%amount%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table budget add amount real";
			util.execSQL(s, a);
		}
		s = "select count(*) from sqlite_master where tbl_name = 'budget' and sql like '%value%' ";
		koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			s = "alter table budget add value real";
			util.execSQL(s, a);
		}
		File exportDir = new File(Environment.getExternalStorageDirectory()+"/CostManager", "pictures");
		if (!exportDir.exists()) {
			exportDir.mkdirs();
		}



		/*
		 * THISMONTH, LASTMONTH, LAST3MONTHS, ALLTIME, THISWEEK, LASTWEEK
		 */
		//execSQL("INSERT into cost_type (code,name) values (1,\"Car Costs\")", a);
	}

	public static boolean PasswordExists(Context c)
	{
		util u = new util();
		u.initializeTables(c);
		String s = getFirstDataString("select pass from password", c);
		if (!s.equals("")){
			return true;
		}else
			return false;
	}
	
	public static boolean ImaPraznihPathova(Context a){
		String s="";
		s = "select count(*) from cost_type where ifnull(path,'') = ''";
		String koliko  = getFirstDataString(s, a); 
		if (koliko.toString().equals("0".toString()))
		{
			return false;
		}else 
			return true;
	}

	public static void AzurirajPraznePathove(Context a){
		util u = new util();
		Cursor c = u.getCursor("select code, path from cost_type where ifnull(path,'') = ''", a);
		String code;
		String path;
		if (c.getCount()>0){
			if (c.moveToFirst()){
				do{
					code = c.getString(0);
					path = getPathOfLevel1(code, a);
					execSQL("update cost_type set path ='"+path+"' where code = "+code, a);
				}while (c.moveToNext());
			};
		}
		c.close();
		u.db.close();
	}
	public static String getPathOfLevel1(String levelID, Context a){//trebalo mi je 4 sata za ovo, al sam izlapio... note to self: sledeci put iskopiraj, konju!
		//metid se dupla, zato sto me mrzi da brisem onaj drugi
		String parent = parentLevelExists(levelID,a);
		if (parent!=""){//ako postoji parent daj ga ako ne onda to je to
			String povratni = getPathOfLevel1(parent,a) +" > "+getLevelName(levelID,a);
			return povratni;
		}else{
			if (levelID.equals("0")){
				levelID = " \\ ";
			}
			return levelID;
		}
	}


	public static void DBInsert(ContentValues cv, Context a, String table){
		SQLiteDatabase db = SQLiteDatabase.openDatabase(a.getDatabasePath("DBS").toString(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		try{
			db.beginTransaction();
			db.insert(table, null, cv);
			db.setTransactionSuccessful();
		}
		catch(Exception ex){
			util.showDialog(ex.getMessage().toString(), a);
		}
		finally{
			db.endTransaction();
		}
	}
	public static void DBUpdate(ContentValues cv, Context a, String table, String where, String[] whereArgs){
		SQLiteDatabase db = SQLiteDatabase.openDatabase(a.getApplicationContext().getDatabasePath("DBS").toString(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);

		try{
			db.beginTransaction();
			db.update(table, cv, where, whereArgs);
			db.setTransactionSuccessful();
		}
		catch(Exception ex){
			util.showDialog(ex.getMessage().toString(), a);
		}
		finally{
			db.endTransaction();
		}
	}
	public boolean typesInserted(Context c){
		boolean inserted;
		String sql = "select code from cost_type";
		if (getFirstDataString(sql, c).equals(""))
			inserted = false;
		else
			inserted = true;
		return inserted;
	}
	public void insertDefaultCostTypes(Context c){
		String s;
		s="insert into cost_type(name,parent) VALUES ('Food',0)";//1
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Clothes',0)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Car',0)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Fun',0)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Bills',0)";
		execSQL(s, c);

		//Food is 1:
		s="insert into cost_type(name,parent) VALUES ('Fast Food',1)";//6
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Groceries',1)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Restaurant',1)";
		execSQL(s, c);

		//Clothes is 2
		s="insert into cost_type(name,parent) VALUES ('Shoes',2)";//9
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Jewels',2)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Shirts',2)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Winter clothes',2)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Summer clothes',2)";
		execSQL(s, c);		

		//Car is 3
		s="insert into cost_type(name,parent) VALUES ('Gasoline',3)";//14
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Maintenance',3)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Insurance',3)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Lease',3)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Registration',3)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Tax',3)";
		execSQL(s, c);

		//Fun is 4
		s="insert into cost_type(name,parent) VALUES ('Vacation',4)";//20
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Gifts',4)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Self improvement',4)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Pets',4)";
		execSQL(s, c);

		//vacation
		s="insert into cost_type(name,parent) VALUES ('Transport',20)"; //24
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Food',20)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Hotel',20)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Misc',20)";
		execSQL(s, c);

		//Bills 5
		s="insert into cost_type(name,parent) VALUES ('Electric',5)";//28
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Phone',5)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Household',5)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Insurance',5)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Gas',5)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Internet',5)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Medical',5)";
		execSQL(s, c);

		//phone
		s="insert into cost_type(name,parent) VALUES ('Phone cellular',29)";//35
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Phone house',29)";
		execSQL(s, c);

		s="insert into cost_type(name,parent) VALUES ('Gas',5)";//37
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Internet',5)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Medical',5)";
		execSQL(s, c);

		//medical:
		s="insert into cost_type(name,parent) VALUES ('Dental',39)";//40
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Eye',39)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Insurance',39)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Perscription',39)";
		execSQL(s, c);
		s="insert into cost_type(name,parent) VALUES ('Vitamines',39)";
		execSQL(s, c);



	}




	public static Pair<String,String> GetLastMonthPeriodDB(){
		String from = null, to = null;
		Calendar f = Calendar.getInstance();
		f.add(Calendar.MONTH, -1);
		f.set(Calendar.DAY_OF_MONTH, 1);

		Calendar t = Calendar.getInstance();
		t.set(Calendar.DAY_OF_MONTH, 1);
		t.add(Calendar.DAY_OF_MONTH, -1);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
		from = sdf.format(f.getTime());
		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);

		return p;
	}
	public static Pair<String,String> GetThisWeekPeriodDB(){
		Calendar f = Calendar.getInstance();
		Calendar t = Calendar.getInstance();
		String from = null, to = null;
		//first day of week
		f.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		from = sdf.format(f.getTime());
		//last day of week
		t.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);

		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);
		return p;
	}
	public static Pair<String,String> GetLastWeekPeriodDB(){
		Calendar f = Calendar.getInstance();
		Calendar t = Calendar.getInstance();
		String from = null, to = null;
		//first day of week
		f.add(Calendar.WEEK_OF_YEAR, -1);
		f.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);

		int year1 = f.get(Calendar.YEAR);
		int month1 = f.get(Calendar.MONTH)+1;
		int day1 = f.get(Calendar.DAY_OF_MONTH);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		from = sdf.format(f.getTime());
		//last day of week
		t.add(Calendar.WEEK_OF_YEAR, -1);
		t.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);

		int year7 = t.get(Calendar.YEAR);
		int month7 = t.get(Calendar.MONTH)+1;
		int day7 = t.get(Calendar.DAY_OF_MONTH); 
		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);
		return p;
	}
	public static Pair<String,String> GetLast2WeekPeriodDB(){
		Calendar f = Calendar.getInstance();
		Calendar t = Calendar.getInstance();
		String from = null, to = null;
		//first day of week
		f.add(Calendar.WEEK_OF_YEAR, -1);
		f.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);

		int year1 = f.get(Calendar.YEAR);
		int month1 = f.get(Calendar.MONTH)+1;
		int day1 = f.get(Calendar.DAY_OF_MONTH);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		from = sdf.format(f.getTime());
		//last day of week
		t.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);

		int year7 = t.get(Calendar.YEAR);
		int month7 = t.get(Calendar.MONTH)+1;
		int day7 = t.get(Calendar.DAY_OF_MONTH); 
		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);
		return p;
	}

	public static Pair<String,String> GetTodayPeriodDB(){
		String from = null, to = null;
		Calendar f = Calendar.getInstance();
		Calendar t = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
		from = sdf.format(f.getTime());
		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);

		return p;
	}

	public static String GetTodayDB(){
		//from is today
		String from = null;
		Calendar f = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
		from = sdf.format(f.getTime());
		return from;
	}


	public static Pair<String,String> GetThisMonthPeriodDB(){
		String from = null, to = null;
		Calendar f = Calendar.getInstance();
		f.set(Calendar.DAY_OF_MONTH, 1);

		Calendar t = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
		from = sdf.format(f.getTime());
		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);

		return p;
	}
	public static Pair<String,String> GetMaxMonthPeriodDB(){
		//first to last day of this month
		String from = null, to = null;
		Calendar f = Calendar.getInstance();
		f.set(Calendar.DAY_OF_MONTH, 1);

		Calendar t = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
		from = sdf.format(f.getTime());
		t.set(Calendar.DATE,t.getActualMaximum(Calendar.DATE));
		to = sdf.format(t.getTime());
		Pair<String, String> p = new Pair<String, String>(from, to);

		return p;
	}

	public static String getYesterdayDB(){
		String yesterday;
		Calendar y = Calendar.getInstance();
		y.add(Calendar.DATE, -1);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		yesterday = sdf.format(y.getTime());
		return yesterday;
	}
	public static String getDayBeforeDB(String day, Context cx){
		try {  
			String dt = day.toString();  // Start date
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Calendar c = Calendar.getInstance();
			c.setTime(sdf.parse(dt));
			c.add(Calendar.DATE, -1);  // number of days to add
			dt = sdf.format(c.getTime());  // dt is now the new date
			return dt;
		} catch (ParseException e){
			Toast.makeText(cx,"Error pasing day before", Toast.LENGTH_SHORT).show();
			return "";
		}
	}

	public static String ConvertToDbFormat(String dat, Context c){
		String reza = "";
		SimpleDateFormat dbFormat = new SimpleDateFormat("yyyy-MM-dd");  
		DateFormat viewFormat = android.text.format.DateFormat.getDateFormat(c);
		try {
			reza = dbFormat.format(viewFormat.parse(dat)); 
		} catch (ParseException e) { 
			e.printStackTrace();
		}
		return reza;
	}

	public static String ConvertToDisplayformattt(String dat){
		String out="";
		out = dat.substring(8, 10)+"."+dat.substring(5, 7)+"."+dat.substring(0, 4);
		return out;
	}

	public static String GetDefaultPeriod(Context c){
		String p;
		p = util.getFirstDataString("select v from setings where k = 'DEFAULTPERIOD'", c);
		return p;
	}

	//Default "From:" date
	public static String GetDefaultFromDateDB(Context c){
		if (GetDefaultPeriod(c).equals("LASTMONTH")){
			return util.GetLastMonthPeriodDB().a.toString();
		}
		if (GetDefaultPeriod(c).equals("THISMONTH")){
			return util.GetThisMonthPeriodDB().a.toString();
		}
		if (GetDefaultPeriod(c).equals("TODAY")){
			return GetTodayDB();
		}
		if (GetDefaultPeriod(c).equals("THISWEEK")){
			return GetThisWeekPeriodDB().a.toString();
		}
		if (GetDefaultPeriod(c).equals("ALLTIME")){
			return "1900-01-01";
		}
		else{ 
			return "";
		}
	}
	public static String getDefaultFromDateView(Context c){
		return dbStringDateToViewStringDate(GetDefaultFromDateDB(c), c);
	}

	//Default "To:" date
	public static String GetDefaultToDateDB(Context c){
		if (GetDefaultPeriod(c).equals("LASTMONTH")){
			return util.GetLastMonthPeriodDB().b.toString();
		}
		if (GetDefaultPeriod(c).equals("THISMONTH")){
			return util.GetThisMonthPeriodDB().b.toString();
		}
		if (GetDefaultPeriod(c).equals("THISWEEK")){
			return util.GetThisWeekPeriodDB().b.toString();
		}
		if (GetDefaultPeriod(c).equals("ALLTIME")){
			return "3000-01-01";
		}
		if (GetDefaultPeriod(c).equals("TODAY")){
			return GetTodayDB();
		}
		else{ 
			return "";
		}
	}

	public static String getDefaultToDateView(Context c){
		return dbStringDateToViewStringDate(GetDefaultToDateDB(c), c);
	}
	public static String gettMaxDayOfMonthView(Context c){
		//first to last day of this month
		String to = null;
		Calendar t = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
		t.set(Calendar.DATE,t.getActualMaximum(Calendar.DATE));
		to = sdf.format(t.getTime());
		to = dbStringDateToViewStringDate(to,c);
		return to;
	}

	public Cursor getCursor(String sql, Context a){
		Cursor c;
		db = SQLiteDatabase.openDatabase(a.getApplicationContext().getDatabasePath("DBS").toString(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		c = null;
		try{
			c = db.rawQuery(sql, null);
			//db.close();//questonable
		}catch (Exception ex){
			showDialog(ex.getMessage().toString(), a);
			db.close();
		}
		return (c);				
	}
	public static String getBracketValue(String input){
		int i1 = input.indexOf("[")+1;
		int i2 = (input.indexOf("]")-input.indexOf("["))-1;
		return String.copyValueOf(input.toCharArray() , i1, i2);
	}
	public static String cutChar(String c){
		if (c.length()>0){
			return (c.substring(0, c.length()-1));
		}
		else 
			return "";
	}
	public static String getSubtypes(String type, Context thisclass){
		String subtypes = null;
		//if (existsChildCost(type,thisclass)){
		subtypes = getAllChildren(type,thisclass);
		//}	
		return subtypes;
	}

	public static boolean existsChildCost(String parent, Context thisclass)
	{
		try{
			Cursor c;
			SQLiteDatabase db;
			db = SQLiteDatabase.openDatabase(thisclass.getApplicationContext().getDatabasePath("DBS").toString(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
			c = null;
			c = db.rawQuery("select * from cost_type where parent = "+parent, null);
			int i = c.getCount(); 
			if (i == 0) {
				c.close();
				db.close();
				return false;
			}
			if (i >= 1){
				//Toast.makeText(this,"FFS: vise", Toast.LENGTH_SHORT).show();
				c.close();
				db.close();
				return true;
			}
			Toast.makeText(thisclass,"Nula slogova, ovo skloniti", Toast.LENGTH_SHORT).show();
			return false;
		}catch (Exception ex){
			util.showDialog(ex.getMessage().toString(),thisclass);
			return false;
		}		
	}
	public static String parentLevelExists(String parent, Context thisclass)
	{
		try{
			Cursor c;
			SQLiteDatabase db;
			db = SQLiteDatabase.openDatabase(thisclass.getApplicationContext().getDatabasePath("DBS").toString(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
			c = null;
			c = db.rawQuery("select * from cost_type where code = "+parent, null);
			int i = c.getCount(); 
			if (i == 0) {
				c.close();
				db.close();
				return "";
			}
			if (i >= 1){
				c.moveToFirst();//znaci ne verujem da treba ovo sranje...
				String ret = c.getString(2); 
				c.close();
				db.close();
				return ret; 
			}
			Toast.makeText(thisclass,"Nula slogova, ovo skloniti", Toast.LENGTH_SHORT).show();
			return "";
		}catch (Exception ex){
			util.showDialog(ex.getMessage().toString(),thisclass);
			return "";
		}		
	}
	public static String getAccountName2(String id, Context thisclass){
		String sql ="select description from account where a = '"+ id.toString()+"'";
		if (getFirstDataString(sql,thisclass) == ""){
			return "Not exists";
		}else{
			return getFirstDataString(sql,thisclass);
		}
	}
	public static String getAccountSumDefaultPeriod(String id, Context thisclass){
		String sql =" select ifnull(sum(l.value),0) from log l "+
				" where ifnull(account,(select v from setings where k = 'DEFAULTACCOUNT')) = "+id+ 
				" and whe >= \'"+util.GetDefaultFromDateDB(thisclass)+"\'"+
				" and whe <= \'"+util.GetDefaultToDateDB(thisclass)+"\'";
		String value = getFirstDataString(sql,thisclass); 
		if ((value == null)||(value == "")){
			return "0";
		}else{
			return getFirstDataString(sql,thisclass);
		}
	}
	public static String getAccountSumCustomPeriod(String id, Context thisclass, String dbDate1, String dbDate2){
		String sql =" select sum(l.value) from log l "+
				" where ifnull(account,(select v from setings where k = 'DEFAULTACCOUNT')) = "+id+ 
				" and whe >= \'"+dbDate1+"\'"+
				" and whe <= \'"+dbDate2+"\'";

		if (getFirstDataString(sql,thisclass) == ""){
			return "0";
		}else{
			return getFirstDataString(sql,thisclass);
		}
	}
	public static String getAccountNameFromCode(String id, Context thisclass){
		String sql ="select a.description as accountname " +
				" from log join cost_type on log.cost_code = cost_type.code " +
				" join account a on a.a = ifnull(log.account,1) "+
				" where log.a = " + id;
		if (getFirstDataString(sql,thisclass) == ""){
			return "Not exists";
		}else{
			return getFirstDataString(sql,thisclass);
		}
	}
	public static String getLevelName(String id, Context thisclass){
		String sql = "select name from cost_type where code = '"+id.toString()+"'";
		if (getFirstDataString(sql,thisclass) == ""){
			return "root";
		}else{
			return getFirstDataString(sql,thisclass);
		}
	}
	public static String getAccountName(String id, Context thisclass){
		String sql = "select description from account where a = '"+id.toString()+"'";
		if (getFirstDataString(sql,thisclass) == ""){
			return "Default";
		}else{
			return getFirstDataString(sql,thisclass);
		}
	}


	public static String getAllChildren(String type, Context thisclass){
		SQLiteDatabase db;
		Cursor c;
		db = SQLiteDatabase.openDatabase(thisclass.getApplicationContext().getDatabasePath("DBS").toString(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		//Log.e("OPEnDB", "7");

		c = db.rawQuery("select code from cost_type where parent ="+type.toString(), null);
		c.moveToFirst();
		if (c.moveToFirst()){
			do{
				type = type.toString()+","+getAllChildren(c.getString(0), thisclass);
			} while (c.moveToNext());
		}else {
			c.close();
			db.close();
			return type;
		}
		c.close();
		db.close();
		return type;
	}
	public static String getCostSum(String types, Context thisclass, String account){
		String result,sql,subTypes;
		String sqlAccount;
		if ((account != null) && (!account.equals("")))
		{
			sqlAccount = " and log.account = "+ account+" ";
		}
		else
		{
			sqlAccount = "";
		}

		subTypes = util.getSubtypes(types, thisclass);
		sql = "select sum(value) as v from log where cost_code in("+subTypes+")" + sqlAccount;
		result = getFirstDataString(sql, thisclass);
		if (result == null)
			result = "0";
		return customFormat("###,##0.00", Float.valueOf(result.trim()).floatValue());
	}

	public static Float getCostSumF(String types, Context thisclass, String account){
		String result,sql,subTypes;
		String sqlAccount;
		if ((account != null) && (!account.equals("")))
		{
			sqlAccount = " and log.account = "+ account+" ";
		}
		else
		{
			sqlAccount = "";
		}
		subTypes = util.getSubtypes(types, thisclass);
		sql = "select sum(value) as v from log where cost_code in("+subTypes+")"+sqlAccount;
		result = getFirstDataString(sql, thisclass);
		if (result == null)
			result = "0";
		return Float.valueOf(result.trim()).floatValue();
	}

	public static String getCostSum(String types, String dateFrom, String dateTo, String account, Context thisclass){
		String result,sql,subTypes;
		subTypes = util.getSubtypes(types, thisclass);
		String sqlAccount;
		if ((account != null) && (!account.equals("")))
		{
			sqlAccount = " and log.account = "+ account+" ";
		}
		else
		{
			sqlAccount = "";
		}

		sql = "select sum(value) as v from log where cost_code in("+subTypes+")"+
				" and log.whe >= '"+dateFrom.toString()+"'"+
				" and log.whe <= '"+dateTo.toString()+"'"+
				sqlAccount;
		result = getFirstDataString(sql, thisclass);
		if (result == null)
			result = "0";
		return customFormat("###,##0.00", Float.valueOf(result.trim()).floatValue());
	}
	
	public static String getBudgetAccountCode(String aCode, Context c){
		String sql = "select account from budget where rowid  = "+aCode;
		String acc = getFirstDataString(sql,c); 
		if (acc.equals("")||acc.equals("0")){
			return getDefaultAccountCode(c);
		}else{
			return getFirstDataString(sql,c);
		}
	}
	
	public static Float getCostSumF(String types, String dateFrom, String dateTo, Context thisclass, String account){
		String result,sql,subTypes, sqlAccount;
		subTypes = util.getSubtypes(types, thisclass);
		if (!account.equals(""))
		{
			sqlAccount = " and log.account = "+account;
		}
		else{
			sqlAccount = " ";
		}
		sql = "select sum(value) as v from log where cost_code in("+subTypes+")"+
				" and log.whe >= '"+dateFrom.toString()+"'"+
				" and log.whe <= '"+dateTo.toString()+"'"+
				sqlAccount;
		result = getFirstDataString(sql, thisclass);
		if (result == null)
			result = "0";
		return Float.valueOf(result.trim()).floatValue();
	}

	static public String customFormat(String pattern, double value ) {
		value = value + 0.0001;
		DecimalFormat myFormatter = new DecimalFormat(pattern);
		String output = myFormatter.format(value);
		return output;
	}
	
	static public float getNumber(String aPattern, String aValue){
		String pattern = aPattern;
		if (pattern == null || pattern.equals("")){
			pattern = "###,##0.00";
		}
		 DecimalFormat format = new DecimalFormat(pattern);
		 ParsePosition parsePosition = new ParsePosition(0);
		 float retVal;
		 Number object = format.parse(aValue, parsePosition);
		 if (object != null){
			 retVal =  object.floatValue();
		 } else
		 {
			 retVal = Float.parseFloat(aValue);
		 }
		 
		return retVal;
	}
	public static String getFirstDataString(String sql, Context thisclass){
		Cursor c;
		try{
			//c = db.rawQuery(sql, null);
			util u = new util();
			c = u.getCursor(sql, thisclass);
			c.moveToFirst();
			if (c.getCount()<0){
				Toast.makeText(thisclass,"How is it possible to have less than zero records?", Toast.LENGTH_LONG).show();
				c.close();
				u.db.close();
				return "";
			}
			if (c.moveToFirst()){
				String r = c.getString(0);
				c.close();
				u.db.close();
				return r;
			}
			else 
				return "";
		}catch (Exception ex){
			showDialog(ex.getMessage().toString(), thisclass);
			return "";
		}
	}
	public static String AddZeros(int i){
		String formatted = String.format("%02d", i);
		return formatted;
	}
	public static String GetCurrentDate(Context c){
		Date dNow = new Date( );
		//	    SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd");
		DateFormat ft = android.text.format.DateFormat.getDateFormat(c);
		return ft.format(dNow);	       
	}

	public String getPathOfLevel(String levelID, Context c){//trebalo mi je 4 sata za ovo, al sam izlapio... note to self: sledeci put iskopiraj, konju!
		String parent = parentLevelExists(levelID,c);
		if (parent!=""){//ako postoji parent daj ga ako ne onda to je to
			String povratni = getPathOfLevel1(parent,c) +" > "+getLevelName(levelID,c);
			return povratni;
		}else{
			if (levelID.equals("0")){
				levelID = "Root";
			}
			return levelID;
		}
	}
	public static String dbStringDateToViewStringDate(String in, Context c){
		DateFormat formatter;
		Date date = null;
		formatter = new SimpleDateFormat("yyyy-MM-dd");
		try {
			date = (Date) formatter.parse(in);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		DateFormat dateFormat = android.text.format.DateFormat.getDateFormat(c);
		return dateFormat.format(date);
	}

	public static String viewStringDateToDBStringDate(String in, Context c){ //View tip je onaj koji gledas, db je bazni datum
		DateFormat formatter;
		Date date = null;
		formatter = android.text.format.DateFormat.getDateFormat(c);//new SimpleDateFormat("yyyy-MM-dd");
		try {
			date = (Date) formatter.parse(in);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");//android.text.format.DateFormat.getDateFormat(c);
		return dateFormat.format(date);
	}



	public static String getTodayDisplayFormat(Context c){
		String currentDateTimeString = DateFormat.getDateInstance().format(new Date());
		// textView is the TextView view that should display it
		return currentDateTimeString;
	}


	//pomera tip prema gore, sortiranje
	//type je nivo na koji si kliknuo, a context je konkretan
	public static void moveTypeUp(int type,String contextId, Context a){
		Cursor c;
		String sql;
		sql = " select name as name, code as _id, code as code, orde"+ 
				" from cost_type "+
				" where parent = "+ Integer.toString(type)+
				" union all select 'Add new type', 1000, 1000, 1000"+ 
				" order by 4 ";
		util u = new util();
		c = u.getCursor(sql, a);
		int pre=0, preOrder = 0, current=0, currentOrder = 0,next=0;
		boolean alredyTop;
		alredyTop = true;
		if (c.getCount()>0){
			if (c.moveToFirst()){
				do{
					if (contextId.equals(c.getString(1))){
						current = c.getInt(1);
						currentOrder = c.getInt(3);
						break; //Nasao si prethodni i sledeci
					}else{
						alredyTop = false; //ovo bi trebalo da se desi inace je kliknuo najgornjeg da me zajebava
						pre = c.getInt(1);
						preOrder = c.getInt(3);
					}
				}while (c.moveToNext());
			};
		}
		c.close();
		u.db.close();
		if (alredyTop){
			return; //zajebava
		}else{
			sql = "update cost_type set orde = "+Integer.toString(preOrder)+" where code ="+Integer.toString(current);
			String	sql2;
			sql2 = "update cost_type set orde = "+Integer.toString(currentOrder)+" where code ="+Integer.toString(pre);
			Log.e("update",sql);
			Log.e("update",sql2);
			execSQL(sql, a);
			execSQL(sql2, a);
		}


	}

	public static BudgetData getBudgetData(Integer id, Context a){
		try{
			util u = new util();
			BudgetData bd = new BudgetData();
			Cursor c = u.getCursor("select cost_type, account, date_from, date_to from budget where rowid = "+ Integer.toString(id), a);
			if (c.getCount()>0){
				if (c.moveToFirst()){
					bd.set_type(Integer.parseInt(c.getString(0)));
					bd.set_account(Integer.parseInt(c.getString(1)));
					bd.set_from(c.getString(2));
					bd.set_to(c.getString(3));
				};
			} 
			c.close();
			u.db.close();
			return bd;
		}catch (Exception ex){
			util.showDialog(ex.getMessage().toString(), a);
			return null;
		}
	}

	/*	AlertDialog.Builder alt_bld = new AlertDialog.Builder(this);
	alt_bld.setMessage("Do you want to close this window ?")
	.setCancelable(false)
	.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
	public void onClick(DialogInterface dialog, int id) {
	// Action for 'Yes' Button
	}
	})
	.setNegativeButton("No", new DialogInterface.OnClickListener() {
	public void onClick(DialogInterface dialog, int id) {
	//  Action for 'NO' Button
	dialog.cancel();
	}
	});
	AlertDialog alert = alt_bld.create();
	// Title for AlertDialog
	alert.setTitle("Title");
	// Icon for AlertDialog
	alert.setIcon(R.drawable.icon);
	alert.show();
	 */
}


